<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjifei extends Common
{

    public function roi($cid='',$nid='',$pid=''){
        $this->assign("nid",$nid);
        if($nid){
            $net=db("cg_net")->where(["id"=>$nid])->find();
        }else{
            $net=[];
        }
        $this->assign("net",$net);
        $this->assign("pid",$pid);
        $cainian=db("cg_cainian")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=Db::table("cg_cainian")->where("id=$cid")->find();
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        if($pid){
            $pro=db("pro_products")->where("id=$pid")->find();
            $pro["profit"]=db("pro_brands b")->join("pro_category c","c.brand_id=b.id")
            ->join("pro_series s","s.category_id=c.id")->join("pro_sku sk","sk.series_id=s.id")
            ->where("sk.id={$pro["sku_id"]}")->value("b.profit");
        }else{
            $pro=[];
        }
        $this->assign("pro",$pro);
        $zongtianshu=($nian["endtime"]-$nian["starttime"])/24/3600+1;

        $kao1=Db::table("cg_kaoheqi1")->where(["cid"=>$nian["id"]])->order("starttime")->select();
        for($i=0;$i<count($kao1);$i++){
            $kao1[$i]["ts"]=($kao1[$i]["endtime"]-$kao1[$i]["starttime"])/24/3600+1;
            $kao1[$i]["kao2"]=Db::table("cg_kaoheqi2")->where(["pid"=>$kao1[$i]["id"]])->count();
            $kao1[$i]["kao2"]=$kao1[$i]["kao2"]+1;
            $kao1[$i]["k2"]=Db::table("cg_kaoheqi2")->where(["pid"=>$kao1[$i]["id"]])->order("starttime")->select();
            $kao1[$i]["base"]=0;$kao1[$i]["fankui"]=0;$kao1[$i]["hanshuis"]=0;$k1jizhun=0;
            for($j=0;$j<count($kao1[$i]["k2"]);$j++){
                $kao1[$i]["k2"][$j]["ts"]=($kao1[$i]["k2"][$j]["endtime"]-$kao1[$i]["k2"][$j]["starttime"])/24/3600+1;
                //网点基准销量
                $yue=(int)$kao1[$i]["k2"][$j]["title"];
                //基准销量
                $wherebase="kaoheqi2=$yue ";
                $wherefb="t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kao1[$i]["k2"][$j]["starttime"]} and td.start<={$kao1[$i]["k2"][$j]["endtime"]} and td.product is not null and td.net_id is not null and td.isOrNo is null ";
                if($nid){
                    $wherebase.="and net_id=$nid ";
                    $wherefb.="and td.net_id=$nid ";
                }
                if($pid){
                    $wherebase.="and pro_id=$pid ";
                    $wherefb.="and td.product=$pid ";
                }
                $base=Db::Table("cg_net_basic")->where($wherebase)->value("num");
                if($base){
                     $kao1[$i]["k2"][$j]["base"]=$base;
                     $kao1[$i]["base"]+=$base;
                }else{
                     $kao1[$i]["k2"][$j]["base"]='-';
                     $base=0;
                }
                //实际反馈
                $fbxl=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($wherefb)->sum("fb_shijixiaoliang");
                if($fbxl){
                     $kao1[$i]["k2"][$j]["fankui"]=$fbxl;
                     $kao1[$i]["fankui"]+=$fbxl;
                }else{
                     $kao1[$i]["k2"][$j]["fankui"]='-';
                     $fbxl=0;
                }
                //费用投入额
                $fbfy=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($wherefb)->sum("fb_shijitourufeiyong");
                if($fbfy){
                     $kao1[$i]["k2"][$j]["hanshuis"]=$fbfy;
                     $kao1[$i]["hanshuis"]+=$fbfy;
                }else{
                     $kao1[$i]["k2"][$j]["hanshuis"]='-';
                     $fbfy=0;
                }
                //预计roi
                if($pid){
                    $jizhun=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($wherefb)->sum("totalBenchmarkSales");
                    if($jizhun){
                        $jizhun=round($jizhun,2);
                    }else{
                        $jizhun=0;
                    }
                    $k1jizhun+=$jizhun;
                    $profit=$pro["profit"];
                    if($fbfy){
                        $kao1[$i]["k2"][$j]["roi"]=round(($kao1[$i]["k2"][$j]["fankui"]-$jizhun)*$profit/$kao1[$i]["k2"][$j]["hanshuis"]/100,2).'%';
                    }else{
                        $kao1[$i]["k2"][$j]["roi"]='-';
                    }
                }else{
                    $kao1[$i]["k2"][$j]["roi"]='-';
                }
                //网点费效比
                if($fbxl){
                    $kao1[$i]["k2"][$j]["bi"]=round($kao1[$i]["k2"][$j]["hanshuis"]/$kao1[$i]["k2"][$j]["fankui"]*100,2).'%';
                }else{
                    $kao1[$i]["k2"][$j]["bi"]="-";
                }
            }
            if($pid){
                $profit=$pro["profit"];
                if($kao1[$i]["hanshuis"]){
                    $kao1[$i]["roi"]=round(($kao1[$i]["fankui"]-$k1jizhun)*$profit/$kao1[$i]["hanshuis"]/100,2).'%';
                }else{
                    $kao1[$i]["roi"]='-';
                }
            }else{
                $kao1[$i]["roi"]='-';
            }
            if($kao1[$i]["fankui"]){
                $kao1[$i]["bi"]=round($kao1[$i]["hanshuis"]/$kao1[$i]["fankui"]*100,2)."%";
            }else{
                $kao1[$i]["bi"]="-";
            }
        }
        $this->assign("kao1",$kao1);
        return $this->fetch();
    }
    //suggest查找网点
    public function netjson()
    {
        $data = Db::table('cg_net')
            ->where("isdel=0")
            ->field('title,id')->select();

        echo json_encode(['message' => 'ok', 'value' => $data, 'code' => '200', 'redirect' => '']);
    }
    //各科目与总费用占比
    public function kemu($cid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $tmp=db("cg_jxs")->field("id")->select();
        $jxs=[];
        foreach ($tmp as $key => $v) {
                # code...
            $jxs[]=$v["id"];
        }
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
     //导出科目
    public function kemudaochu($cid){
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
       
        $tmp=db("cg_jxs")->field("id")->select();
        $jxs=[];
        foreach ($tmp as $key => $v) {
                # code...
            $jxs[]=$v["id"];
        }
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        //导出处理
        $eid=Session::get("employee_id");
        $users=Db::table("employee")->where(["id"=>$eid])->find();
        $title="{$cn["title"]}财年各科目与总费用占比";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
         $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
             $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:E2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
     
      


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"一级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"二级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"三级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"占比");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"投入额");
        $h=3;
        for($i=0;$i<count($kemu);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$kemu[$i]["kemu1"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$kemu[$i]["kemu2"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kemu[$i]["kemu3"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kemu[$i]["bili"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kemu[$i]["jine"],PHPExcel_Cell_DataType::TYPE_STRING);      
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:E'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
             //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
               $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        //var_dump($objWriter);exit;
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //日常预算1
    public function rcys1($cid='',$page=1,$list=10){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $request = Request::instance();
        $data = $request->param();
        $where="type=3 and jine<>0 and cainian=$cid ";
        $td=["一级费用科目"];
        $group=["depart","yijifeiyong"];$bumenarr=[];$yijiarr=[];$erjiarr=[];$sanjiarr=[];
        if($data){
            if($data["kemu"]==2){
                $td[]="二级费用科目";
                $group[]="erjifeiyong";
                //二级费用科目
                if($data["erjifeiyong"]){
                    $kemu2=implode(',',explode('-',$data["erjifeiyong"]));
                    $where.="and erjifeiyong in ($kemu2) ";
                    $erjiarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                }
                $data["yijifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else if($data["kemu"]==3){
                $td[]="二级费用科目";
                $group[]="erjifeiyong";
                $td[]="三级费用科目";
                $group[]="sanjifeiyong";
                //三级费用科目
                if($data["sanjifeiyong"]){
                    $kemu3=implode(',',explode('-',$data["sanjifeiyong"]));
                    $where.="and sanjifeiyong in ($kemu3) ";
                    $sanjiarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else{
                //一级费用科目
                if($data["yijifeiyong"]){
                    $kemu1=implode(',',explode('-',$data["yijifeiyong"]));
                    $where.="and yijifeiyong in ($kemu1) ";
                    $yijiarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";

            }
            if($data["cainian"]==1){
                $td[]="一级考核期";
                $group[]="yijikaoheqi";
            }else if($data["cainian"]==2){
                $td[]="一级考核期";
                $td[]="二级考核期";
                $group[]="yijikaoheqi";
                $group[]="erjikaoheqi";
            }
            if(isset($data["erjikaoheqi"])){
                if($data["erjikaoheqi"]){
                    $where.="and erjikaoheqi={$data["erjikaoheqi"]} ";
                }
            }
            if(isset($data["yijikaoheqi"])){
                if($data["yijikaoheqi"]){
                    $where.="and yijikaoheqi={$data["yijikaoheqi"]} ";
                }
            }
            if($data["bumen"]){

                $bumen=implode(',',explode('-',$data["bumen"]));
                $where.="and depart in ($bumen) ";
                $bumenarr=db("department")->where("id in ($bumen)")->select();
            }

        }else{
            $data=["bumen"=>"","cainian"=>0,"kemu"=>1,"yijifeiyong"=>"","erjifeiyong"=>"","sanjifeiyong"=>""];
        }
        $this->assign("bumenarr",$bumenarr);
        $this->assign("yijiarr",$yijiarr);
        $this->assign("erjiarr",$erjiarr);
        $this->assign("sanjiarr",$sanjiarr);
        $this->assign("data",$data);
        $this->assign("td",$td);
        $gp=implode(',',$group);
        $sum=db("yusuan")->where($where)->group($gp)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $arr=db("yusuan")->where($where)->group($gp)->limit($start,$list)->field("$gp,sum(jine) as jine")->select();
        foreach ($arr as $k => $v) {
                # code...
                $wheres="type=3 and jine<>0 and cainian=$cid ";
                foreach ($group as $kg => $vg) {
                    # code...
                    if($vg=="depart"){
                        if($v["depart"])
                            $wheres.="and depart={$v["depart"]} ";
                        else
                            $wheres.="and depart is null";
                    }
                    
                    if($vg=="yijifeiyong"){
                        if($v["yijifeiyong"])
                            $wheres.="and yijifeiyong={$v["yijifeiyong"]} ";
                        else
                            $wheres.="and yijifeiyong is null ";
                    }
                    if($vg=="erjifeiyong"){
                        if($v["erjifeiyong"])
                            $wheres.="and erjifeiyong={$v["erjifeiyong"]} ";
                        else
                            $wheres.="and erjifeiyong is null ";
                    }
                    if($vg=="sanjifeiyong"){
                        if($v["sanjifeiyong"])
                            $wheres.="and sanjifeiyong={$v["sanjifeiyong"]} ";
                        else
                            $wheres.="and sanjifeiyong is null ";
                    }
                    if($vg=="yijikaoheqi"){
                        if($v["yijikaoheqi"])
                            $wheres.="and yijikaoheqi={$v["yijikaoheqi"]} ";
                        else
                            $wheres.="and yijikaoheqi is null ";
                    }
                        
                    if($vg=="erjikaoheqi"){
                        if($v["erjikaoheqi"])
                            $wheres.="and erjikaoheqi={$v["erjikaoheqi"]} ";
                        else
                            $wheres.="and erjikaoheqi is null ";
                    }
                }
                //echo $wheres."<br>";
                $tmp=db("yusuan")->where($wheres)->field("id")->select();
                $arr[$k]["ids"]=[];
                foreach ($tmp as $kt => $vt) {
                    # code...
                    $arr[$k]["ids"][]=$vt["id"];
                }
                $arr[$k]["ids"]=implode(',',$arr[$k]["ids"]);
            }
        for ($i=0; $i <count($arr) ; $i++) {
            $pifu=db("yusuan_detail")->where("yid in ({$arr[$i]["ids"]}) and type=2")->sum("jine");
            if($pifu)
                $arr[$i]["pifu"]=round($pifu,2);
            else
                $arr[$i]["pifu"]=0;
            $arr[$i]["weiyong"]=$arr[$i]["jine"]-$arr[$i]["pifu"];
            $sqz=db("yusuan_detail")->where("yid in ({$arr[$i]["ids"]}) and type=1")->sum("jine");
            if($sqz)
                $arr[$i]["sqz"]=round($sqz,2);
            else
                $arr[$i]["sqz"]=0;
            $index=$i+1;
            $arr[$i]["tr"]="<tr><td>{$index}</td>";
            foreach ($group as $k => $v) {
                # code...
                
                if($v=="yijifeiyong"){
                    if($arr[$i]["yijifeiyong"]){
                        $tmpyijifeiyong=db("cg_feiyongkemu1")->where(["id"=>$arr[$i]["yijifeiyong"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpyijifeiyong}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="erjifeiyong"){
                    if($arr[$i]["erjifeiyong"]){
                        $tmperjifeiyong=db("cg_feiyongkemu2")->where(["id"=>$arr[$i]["erjifeiyong"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmperjifeiyong}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="sanjifeiyong"){
                    if($arr[$i]["sanjifeiyong"]){
                        $tmpsanjifeiyong=db("cg_feiyongkemu3")->where(["id"=>$arr[$i]["sanjifeiyong"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpsanjifeiyong}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="yijikaoheqi"){
                    if($arr[$i]["yijikaoheqi"]){
                        $tmpyijikaoheqi=db("cg_kaoheqi1")->where(["id"=>$arr[$i]["yijikaoheqi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpyijikaoheqi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="erjikaoheqi"){
                    if($arr[$i]["erjikaoheqi"]){
                        $tmperjikaoheqi=db("cg_kaoheqi2")->where(["id"=>$arr[$i]["erjikaoheqi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmperjikaoheqi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
            }
            if($arr[$i]["depart"]){
                $tmpdepart=db("department")->where(["id"=>$arr[$i]["depart"]])->value("de_name");
                $arr[$i]["tr"].="<td>{$tmpdepart}</td>";
            }else{
                $arr[$i]["tr"].="<td></td>";
            }
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan1'>{$arr[$i]["jine"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan2'>{$arr[$i]["pifu"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan3'>{$arr[$i]["weiyong"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan4'>{$arr[$i]["sqz"]}</td>";
            if($arr[$i]["pifu"]>=$arr[$i]["jine"]){
                 $arr[$i]["tr"].="<td><div class='Redlay'>红灯</div></td>";
            }else if($arr[$i]["pifu"]+$arr[$i]["sqz"]>=$arr[$i]["jine"]){
                $arr[$i]["tr"].="<td><div class='Yellowlay'>黄灯</div></td>";
            }else{
                $arr[$i]["tr"].="<td><div class='Greenlay'>绿灯</div></td>";
            }
            $arr[$i]["tr"].="</tr>";
        }
        $this->assign("arr",$arr);
         $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    public function rcys2($cid='',$k1id='',$k2id=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        $this->assign("k1id",$k1id);
        if($k1id){
            $kaohe2=db("cg_kaoheqi2")->where(["pid"=>$k1id])->order("starttime")->select();
        }else{
            $kaohe2=[];
        }
        $this->assign("kaohe2",$kaohe2);
        $this->assign("k2id",$k2id);
        $request = Request::instance();
        $data = $request->post();
        $where="type=3 and jine<>0 and cainian=$cid ";
        if($k1id){
            $where.="and yijikaoheqi=$k1id ";
        }
        if($k2id){
            $where.="and erjikaoheqi=$k2id ";
        }
        $bumenarr=[];$yijiarr=[];$erjiarr=[];$sanjiarr=[];$kemuarr=[];

        if($data){
            if($data["bumen"]){

                $bumen=implode(',',explode('-',$data["bumen"]));
                $where.="and depart in ($bumen) ";
                $bumenarr=db("department")->where("id in ($bumen)")->select();
            }
             //科目纬度
            if($data["kemu"]==2){
                //二级费用科目
                if($data["erjifeiyong"]){
                    $kemu2=implode(',',explode('-',$data["erjifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                    $erjiarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                }
                $data["yijifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else if($data["kemu"]==3){
               
                //三级费用科目
                if($data["sanjifeiyong"]){
                    $kemu3=implode(',',explode('-',$data["sanjifeiyong"]));
                   
                    $kemuarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                    $sanjiarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else{
                //一级费用科目
                if($data["yijifeiyong"]){
                    $kemu1=implode(',',explode('-',$data["yijifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                    $yijiarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";

            }
        }else{
            $data=["bumen"=>"","kemu"=>1,"yijifeiyong"=>"","erjifeiyong"=>"","sanjifeiyong"=>""];
        }
        if($k2id){
           // echo $k2id;
            $kaohedian=db("cg_kaoheqi2")->where(["id"=>$k2id])->find();
        }else if($k1id){
            //echo $k1id;
            $kaohedian=db("cg_kaoheqi1")->where(["id"=>$k1id])->find();
        }else{
            //echo $cid;
            $kaohedian=db("cg_cainian")->where(["id"=>$cid])->find();
        }
        foreach ($kemuarr as $k => $v) {
            # code...
            $kemuarr[$k]["ys"]=0;$kemuarr[$k]["sy"]=0;$kemuarr[$k]["sqz"]=0;$kemuarr[$k]["jy"]=0;
        }
        $zong["ys"]=0;$zong["sy"]=0;$zong["sqz"]=0;$zong["jy"]=0;
        for($i=0;$i<count($bumenarr);$i++){
            $bumenarr[$i]["zys"]=0;$bumenarr[$i]["zsy"]=0;$bumenarr[$i]["zsqz"]=0;$bumenarr[$i]["zjy"]=0;
            $bumenarr[$i]["ys"]=[];$bumenarr[$i]["sy"]=[];$bumenarr[$i]["sqz"]=[];$bumenarr[$i]["jy"]=[];
            for($j=0;$j<count($kemuarr);$j++){
                //预算
                $whereys="y.type=3 and y.jine<>0 and y.depart={$bumenarr[$i]["id"]} ";
                if($data["kemu"]==3){
                    $whereys.="and y.sanjifeiyong={$kemuarr[$j]["id"]} ";
                }else if($data["kemu"]==2){
                    $whereys.="and y.erjifeiyong={$kemuarr[$j]["id"]} ";
                }else{
                    $whereys.="and y.yijifeiyong={$kemuarr[$j]["id"]} ";
                }
                if($k2id){
                    $whereys.="and y.erjikaoheqi=$k2id ";
                }else if($k1id){
                    $whereys.="and y.yijikaoheqi=$k1id ";
                }else{
                   $whereys.="and y.cainian=$cid ";
                }
                $ys=db("yusuan y")->where($whereys)->sum("y.jine");
                $sy=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($whereys." and yd.type=2")->sum("yd.jine");
                $sqz=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($whereys." and yd.type=1")->sum("yd.jine");
                if($ys){
                    $bumenarr[$i]["ys"][$j]=$ys;
                }else{
                    $bumenarr[$i]["ys"][$j]=0;
                }
                if($sy){
                    $bumenarr[$i]["sy"][$j]=$sy;
                }else{
                    $bumenarr[$i]["sy"][$j]=0;
                }
                if($sqz){
                    $bumenarr[$i]["sqz"][$j]=$sqz;
                }else{
                    $bumenarr[$i]["sqz"][$j]=0;
                }
                $bumenarr[$i]["jy"][$j]=$bumenarr[$i]["ys"][$j]-$bumenarr[$i]["sy"][$j];
                $bumenarr[$i]["zys"]+=$bumenarr[$i]["ys"][$j];
                $bumenarr[$i]["zsy"]+=$bumenarr[$i]["sy"][$j];
                $bumenarr[$i]["zsqz"]+=$bumenarr[$i]["sqz"][$j];
                $bumenarr[$i]["zjy"]+=$bumenarr[$i]["jy"][$j];

                $kemuarr[$j]["ys"]+=$bumenarr[$i]["ys"][$j];
                $kemuarr[$j]["sy"]+=$bumenarr[$i]["sy"][$j];
                $kemuarr[$j]["sqz"]+=$bumenarr[$i]["sqz"][$j];
                $kemuarr[$j]["jy"]+=$bumenarr[$i]["jy"][$j];
                $zong["ys"]+=$bumenarr[$i]["ys"][$j];
                $zong["sy"]+=$bumenarr[$i]["sy"][$j];
                $zong["sqz"]+=$bumenarr[$i]["sqz"][$j];
                $zong["jy"]+=$bumenarr[$i]["jy"][$j];
            }
        }
        $this->assign("zong",$zong);
        $this->assign("kemuarr",$kemuarr);
        $this->assign("kaohedian",$kaohedian);
        $this->assign("data",$data);
        $this->assign("bumenarr",$bumenarr);
        $this->assign("yijiarr",$yijiarr);
        $this->assign("erjiarr",$erjiarr);
        $this->assign("sanjiarr",$sanjiarr);
        return $this->fetch();
    }
    public function rcys3($cid='',$k1id=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        $this->assign("k1id",$k1id);
        $request = Request::instance();
        $data = $request->post();
         $bumenarr=[];$yijiarr=[];$erjiarr=[];$sanjiarr=[];$kemuarr=[];
        if($data){
            if($data["bumen"]){
                $bumen=implode(',',explode('-',$data["bumen"]));
                $bumenarr=db("department")->where("id in ($bumen)")->select();
            }
             //科目纬度
            if($data["kemu"]==2){
                //二级费用科目
                if($data["erjifeiyong"]){
                    $kemu2=implode(',',explode('-',$data["erjifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                    $erjiarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu2")->select();
                    $erjiarr=[];
                }
                $data["yijifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else if($data["kemu"]==3){
               
                //三级费用科目
                if($data["sanjifeiyong"]){
                    $kemu3=implode(',',explode('-',$data["sanjifeiyong"]));
                   
                    $kemuarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                    $sanjiarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu3")->select();
                    $sanjiarr=[];
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else{
                //一级费用科目
                if($data["yijifeiyong"]){
                    $kemu1=implode(',',explode('-',$data["yijifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                    $yijiarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu1")->select();
                    $yijiarr=[];
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";

            }
        }else{
            $data=["bumen"=>"","kemu"=>1,"yijifeiyong"=>"","erjifeiyong"=>"","sanjifeiyong"=>""];
        }
        if($k1id){
            //echo $k1id;
            $kaohedian=db("cg_kaoheqi2")->where(["pid"=>$k1id])->select();
        }else{
            //echo $cid;
            $kaohedian=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        }
        foreach ($kaohedian as $k => $v) {
            # code...
            $kaohedian[$k]["ys"]=0;
            $kaohedian[$k]["sy"]=0;
            $kaohedian[$k]["wsy"]=0;
        }
        $all=["ys"=>0,"sy"=>0,"wsy"=>0];
        if($bumenarr&&$kemuarr){
            for($i=0;$i<count($bumenarr);$i++){
                //合并行
                $bumenarr[$i]["rol"]=count($kemuarr)*3;
                $bumenarr[$i]["kemu"]=$kemuarr;
                for($j=0;$j<count($kemuarr);$j++){
                    $bumenarr[$i]["kemu"][$j]["zys"]=0;$bumenarr[$i]["kemu"][$j]["zsy"]=0;
                    //科目纬度
                    for($k=0;$k<count($kaohedian);$k++){
                        //时间纬度
                        //预算条件
                        $whereys="y.type=3 and y.jine<>0 and y.depart={$bumenarr[$i]["id"]} ";
                        if($data["kemu"]==1){
                            $whereys.="and y.yijifeiyong={$kemuarr[$j]["id"]} ";
                        }else if($data["kemu"]==2){
                            $whereys.="and y.erjifeiyong={$kemuarr[$j]["id"]} ";
                        }else{
                            $whereys.="and y.sanjifeiyong={$kemuarr[$j]["id"]} ";
                        }
                        if($k1id){
                            $whereys.="and y.erjikaoheqi={$kaohedian[$k]["id"]}";
                        }else{
                            $whereys.="and y.yijikaoheqi={$kaohedian[$k]["id"]}";
                        }
                        $ys=db("yusuan y")->where($whereys)->sum("y.jine");
                        if($ys){
                            $bumenarr[$i]["kemu"][$j]["ys"][$k]=$ys;
                            $bumenarr[$i]["kemu"][$j]["zys"]+=$ys;
                            $kaohedian[$k]["ys"]+=$ys;
                            $kaohedian[$k]["wsy"]+=$ys;
                            $all["ys"]+=$ys;
                            $all["wsy"]+=$ys;
                        }else{
                            $bumenarr[$i]["kemu"][$j]["ys"][$k]=0;
                        }
                        //预算使用
                        $sy=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($whereys." and yd.type=2")->sum("yd.jine");
                        if($sy){
                            $bumenarr[$i]["kemu"][$j]["sy"][$k]=$sy;
                            $bumenarr[$i]["kemu"][$j]["zsy"]+=$sy;
                            $kaohedian[$k]["sy"]+=$sy;
                            $kaohedian[$k]["wsy"]-=$sy;
                            $all["sy"]+=$sy;
                            $all["wsy"]-=$sy;
                        }else{
                            $bumenarr[$i]["kemu"][$j]["sy"][$k]=0;
                        }
                        $bumenarr[$i]["kemu"][$j]["wsy"][$k]=$bumenarr[$i]["kemu"][$j]["ys"][$k]-$bumenarr[$i]["kemu"][$j]["sy"][$k];
                    }
                    $bumenarr[$i]["kemu"][$j]["zwsy"]=$bumenarr[$i]["kemu"][$j]["zys"]-$bumenarr[$i]["kemu"][$j]["zsy"];
                }
            }
        }
        
        $this->assign("all",$all);
        $this->assign("kaohedian",$kaohedian);
        $this->assign("data",$data);
        $this->assign("kemuarr",$kemuarr);
        $this->assign("bumenarr",$bumenarr);
        $this->assign("yijiarr",$yijiarr);
        $this->assign("erjiarr",$erjiarr);
        $this->assign("sanjiarr",$sanjiarr);
        return $this->fetch();
    }
}